import pymysql
from config import *


class DBUtil_authority:
    __conn = None

    @classmethod
    def __get_conn(cls):
        if DBUtil_authority.__conn is None:
            DBUtil_authority.__conn = pymysql.connect(host=databasehost_34, port=databaseport, user="root",
                                                      password=databasepasswd, database=database_authority,
                                                      charset="utf8")
        return DBUtil_authority.__conn

    @classmethod
    def __close_conn(cls):
        if DBUtil_authority.__conn:
            DBUtil_authority.__conn.close()
            DBUtil_authority.__conn = None  # 当数据库关闭后，数据库的值并不是空

    @classmethod
    def select_one(cls, sql):
        cursor = None
        try:
            conn = DBUtil_authority.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_authority.__close_conn()
        return result

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            conn = DBUtil_authority.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            if DBUtil_authority.__conn:
                DBUtil_authority.__conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_authority.__close_conn()


class DBUtil_course:
    __conn = None

    @classmethod
    def __get_conn(cls):
        if DBUtil_course.__conn is None:
            DBUtil_course.__conn = pymysql.connect(host=databasehost_32, port=databaseport, user=databaseuser,
                                                   password=databasepasswd, database=database_course, charset="utf8")
        return DBUtil_course.__conn

    @classmethod
    def __close_conn(cls):
        if DBUtil_course.__conn:
            DBUtil_course.__conn.close()
            DBUtil_course.__conn = None  # 当数据库关闭后，数据库的值并不是空

    @classmethod
    def select_one(cls, sql):
        cursor = None
        try:
            conn = DBUtil_course.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_course.__close_conn()
        return result

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            conn = DBUtil_course.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            if DBUtil_course.__conn:
                DBUtil_course.__conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_course.__close_conn()


class DBUtil_teaching:
    __conn = None

    @classmethod
    def __get_conn(cls):
        if DBUtil_teaching.__conn is None:
            DBUtil_teaching.__conn = pymysql.connect(host=databasehost_32, port=databaseport, user=databaseuser,
                                                     password=databasepasswd, database=database_teaching,
                                                     charset="utf8")
        return DBUtil_teaching.__conn

    @classmethod
    def __close_conn(cls):
        if DBUtil_teaching.__conn:
            DBUtil_teaching.__conn.close()
            DBUtil_teaching.__conn = None  # 当数据库关闭后，数据库的值并不是空

    @classmethod
    def select_one(cls, sql):
        cursor = None
        try:
            conn = DBUtil_teaching.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_teaching.__close_conn()
        return result

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            conn = DBUtil_teaching.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            if DBUtil_teaching.__conn:
                DBUtil_teaching.__conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_teaching.__close_conn()


class DBUtil_cloud:
    __conn = None

    @classmethod
    def __get_conn(cls):
        if DBUtil_cloud.__conn is None:
            DBUtil_cloud.__conn = pymysql.connect(host=databasehost_34, port=databaseport, user=databaseuser,
                                                  password=databasepasswd, database=database_cloud, charset="utf8")
        return DBUtil_cloud.__conn

    @classmethod
    def __close_conn(cls):
        if DBUtil_cloud.__conn:
            DBUtil_cloud.__conn.close()
            DBUtil_cloud.__conn = None  # 当数据库关闭后，数据库的值并不是空

    @classmethod
    def select_one(cls, sql):
        cursor = None
        try:
            conn = DBUtil_cloud.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_cloud.__close_conn()
        return result

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            conn = DBUtil_cloud.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            if DBUtil_cloud.__conn:
                DBUtil_cloud.__conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_cloud.__close_conn()


class DBUtil_tea_quality_evaluation:
    __conn = None

    @classmethod
    def __get_conn(cls):
        if DBUtil_tea_quality_evaluation.__conn is None:
            DBUtil_tea_quality_evaluation.__conn = pymysql.connect(host=databasehost_32, port=databaseport,
                                                                   user=databaseuser,
                                                                   password=databasepasswd,
                                                                   database=database_tea_quality_evaluation,
                                                                   charset="utf8")
        return DBUtil_tea_quality_evaluation.__conn

    @classmethod
    def __close_conn(cls):
        if DBUtil_tea_quality_evaluation.__conn:
            DBUtil_tea_quality_evaluation.__conn.close()
            DBUtil_tea_quality_evaluation.__conn = None  # 当数据库关闭后，数据库的值并不是空

    @classmethod
    def select_one(cls, sql):
        cursor = None
        try:
            conn = DBUtil_tea_quality_evaluation.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_tea_quality_evaluation.__close_conn()
        return result

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            conn = DBUtil_tea_quality_evaluation.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            if DBUtil_tea_quality_evaluation.__conn:
                DBUtil_tea_quality_evaluation.__conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_tea_quality_evaluation.__close_conn()


class DBUtil_xyzc:
    __conn = None

    @classmethod
    def __get_conn(cls):
        if DBUtil_xyzc.__conn is None:
            DBUtil_xyzc.__conn = pymysql.connect(host=databasehost_46,
                                                 port=databaseport,
                                                 user=databaseuser,
                                                 password=databasepasswd1,
                                                 database=database_xyzc,
                                                 charset="utf8")
        return DBUtil_xyzc.__conn

    @classmethod
    def __close_conn(cls):
        if DBUtil_xyzc.__conn:
            DBUtil_xyzc.__conn.close()
            DBUtil_xyzc.__conn = None  # 当数据库关闭后，数据库的值并不是空

    @classmethod
    def select_one(cls, sql):
        cursor = None
        try:
            conn = DBUtil_xyzc.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_xyzc.__close_conn()
        return result

    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            conn = DBUtil_xyzc.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            if DBUtil_xyzc.__conn:
                DBUtil_xyzc.__conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            DBUtil_xyzc.__close_conn()


if __name__ == '__main__':
    sql = "SELECT count(*) FROM tb_laboratory_question_library WHERE id = '84'"
    re = DBUtil_teaching.select_one(sql)
    print(re[0])
    print(type(re))
